| ID-10t Consulants | |||||
| Data Dictionary | |||||
| TABLE | COLUMN | DATA TYPE | DESCRIPTION | NOTES | VALIDATION |
| students | std_id | VARCHAR(8) | Siena College network login -first & middle initial -the day of the month you were born (two digits) -the first four letters of your last name |
Primary Key Varations/Exceptions: -If you have no middle initial, just skip it -if you have an apostrophie in your name (e.g., O'Brien), leave it out -if your last name is less than four letters, write what you have |
AlphaNumeric |
| students | std_email | VARCHAR(30) | student's e-mail address | ||
| students | std_fname | VARCHAR(20) | student's first name | ||
| students | std_lname | VARCHAR(20) | student's last name | ||
| students | std_password | VARCHAR(15) | student's password | Chosen by student at account creation | AlphaNumeric |
| students | secret_question | VARCHAR(50) | student's secret question | ||
| students | secret_answer | VARCHAR(30) | student's answer to secret question | ||
| instructor | inst_id | VARCHAR(8) | instructor's login user id | Primary Key - Created by system administrator at account creation |
AlphaNumeric |
| instructor | inst_email | VARCHAR(30) | instructor's e-mail address | ||
| instructor | inst_fname | VARCHAR(20) | instructor's first name | ||
| instructor | inst_lname | VARCHAR(20) | instructor's last name | ||
| instructor | inst_password | VARCHAR(15) | instructor's password | Chosen by system administrator at account creation | AlphaNumeric |
| instructor | private_pool_id | AUTONUMBER | isntructor's private pool | Foreign Key referencing private_pool_id from Private Pool | |
| instructor | secret_question | VARCHAR(50) | instructor's secret question | ||
| instructor | secret_answer | VARCHAR(30) | instructor's answer to secret question | ||
| sys_adm | sys_id | VARCHAR(10) | system administrator's login user id | Primary Key - Chosen by the system administrator |
AlphaNumeric |
| sys_adm | sys_email | VARCHAR(30) | system administrator's e-mail address | ||
| sys_adm | sys_fname | VARCHAR(20) | system administrator's first name | ||
| sys_adm | sys_lname | VARCHAR(20) | system administrator's last name | ||
| sys_adm | sys_password | VARCHAR(15) | system administrator's password | Chosen by the system administrator | AlphaNumeric |
| sys_adm | private_pool_id | AUTONUMBER | system amdinistator's private pool | Foreign Key referencing private_pool_id from Private Pool | |
| sys_adm | secret_question | VARCHAR(50) | system administrator's secret question | ||
| sys_adm | secret_answer | VARCHAR(30) | system administrator's answer to secret question | ||
| prg_adm | prg_id | VARCHAR(8) | program administrator's login user id | Primary Key - Created by system administrator at account creation |
AlphaNumeric |
| prg_adm | prg_email | VARCHAR(30) | program administrator's e-mail address | ||
| prg_adm | prg_fname | VARCHAR(20) | program administrator's first name | ||
| prg_adm | prg_lname | VARCHAR(20) | program administrator's last name | ||
| prg_adm | prg_password | VARCHAR(15) | program administrator's password | Chosen by the system administrator at account creation | AlphaNumeric |
| prg_adm | private_pool_id | AUTONUMBER | program administrator's private pool | Foreign Key referencing private_pool_id from Private Pool | |
| prg_adm | secret_question | VARCHAR(50) | program administrator's secret question | ||
| prg_adm | secret_answer | VARCHAR(30) | program administrator's answer to secret question | ||
| course | course_id | AUTONUMBER | course id number | Primary Key | |
| course | course_name | VARCHAR(30) | course name | Example: Intro to Computer Science | |
| course | course_number | CHAR(7) | course number | Example: CSIS110 | AlphaNumeric |
| section | section_id | AUTONUMBER | section id number | Primary Key | |
| section | section_number | CHAR(2) | section number | ||
| section | course_id | CHAR(7) | course id number | Foreign Key referencing course_id from Course | |
| section | section_semester | VARCHAR(10) | semester the section is offered | ||
| section | section_year | NUMBER | year the section is offered | ||
| section | section_password | VARCHAR(10) | password to enroll in the section | ||
| teaching | inst_id | VARCHAR(8) | instructor teaching the section | Foreign Key referencing inst_id from Instructor | AlphaNumeric |
| teaching | section_id | AUTONUMBER | course and section being taught | Foreign Key referencing section_id from Section | |
| coordinating | prg_id | VARCHAR(8) | coordinating program administrator | Foreign Key referencing prg_id from Prg_Adm | AlphaNumeric |
| coordinating | course_id | AUTONUMBER | course that is coordinated | Foreign Key referencing course_id from Course | |
| enroll | std_id | VARCHAR(8) | student enrolled in the section | Foreign Key referencing std_id from Student | AlphaNumeric |
| enroll | section_id | AUTONUMBER | course and section students are enrolled | Foreign Key referencing section_id from Section | |
| question_set | set_name | VARCHAR(20) | question set name | ||
| question_set | set_type | VARCHAR(8) | question set type | Types available: -Test -Quiz -Homework -Practice |
|
| question_set | set_id | AUTONUMBER | question set id number | Primary Key | |
| published_set | set_name | VARCHAR(20) | question set name | ||
| published_set | time_avail | TIME | the time the question set becomes available | Chosen at publication of the question set | |
| published_set | time_due | TIME | the time the question set is due or becomes unavailable | Chosen at publication of the question set | |
| published_set | date_avail | DATE | the date the question set becomes available | Chosen at publication of the question set | |
| published_set | date_due | DATE | the date the question set is due or becomes unavailable | Chosen at publication of the question set | |
| published_set | time_limit | TIME | how long a user has to submit the question set once the user has started answering the question set | Chosen at publication of the question set. This is an optional field and can be left empty | |
| published_set | num_of_attempts | NUMBER | the number of attempts a user has per question | Chosen at publication of the question set | |
| published_set | late_submission | YES/NO | whether a published question set accepts late submissions | Chosen at publication of the question set. Yes or No will be entered into this field | |
| published_set | visible | YES/NO | whether a published question set is visible to users after the date and time due | Chosen at publication of the question set. Yes or No will be entered into this field | |
| published_set | publish_id | AUTONUMBER | published question set id number | Primary Key | |
| question | qst_id | AUTONUMBER | question id number | Primary Key | |
| question | qst_title | VARCHAR(30) | question title | ||
| question | qst_category | VARCHAR(15) | question category | Example categories: | |
| question | question | TEXT | question text | ||
| question | signature | TEXT | question signature | ||
| question | solution | TEXT | question solution | ||
| question | recursive | YES/NO | whether the question is recursive or not | ||
| hints | qst_id | AUTONUMBER | question the hint belongs to | Foreign Key referencing qst_id from Question | |
| hints | hint_id | AUTONUMBER | hint id number | Primary Key | |
| hints | hint | TEXT | hint text | ||
| test_case | qst_id | AUTONUMBER | question the test case belongs to | Foreign Key referencing qst_id from Question | |
| test_case | test_case_id | AUTONUMBER | test case id number | Primary Key | |
| test_case | output | TEXT | test case output | ||
| test_case | parameters | TEXT | test case parameters | ||
| test_case | hidden | YES/NO | whether the test case is hidden or not | ||
| pset_contains | publish_id | AUTONUMBER | published question set holding the question | Foreign Key referencing publish_id from Published Set | |
| pset_contains | qst_id | AUTONUMBER | question in the published question set | Foreign Key referencing qst_id from Question | |
| set_contains | set_id | AUTONUMBER | question set holding the question | Foreign Key referencing set_id from Question Set | |
| set_contains | qst_id | AUTONUMBER | question in the question set | ||
| private_pool | private_pool_id | AUTONUMBER | private pool id number | Primary Key | |
| course_pool | course_pool_id | AUTONUMBER | course pool id number | Primary Key | |
| course_pool | course_id | AUTONUMBER | course the course pool belongs to | Foreign Key referencing course_id from Course | |
| prvtpool_question | private_pool_id | AUTONUMBER | private pool holding the question | Primary Key | |
| prvtpool_question | qst_id | AUTONUMBER | question in the private pool | Foreign Key referencing qst_id from Question | |
| prvtpool_set | private_pool_id | AUTONUMBER | private pool holding the question set | Foreign Key referencing private_pool_id from Private Pool | |
| prvtpool_set | set_id | AUTONUMBER | question set in the private pool | Foreign Key referencing set_id from Question Set | |
| gpool_question | qst_id | AUTONUMBER | question in the global pool | Foreign Key referencing qst_id from Question | |
| gpool_set | set_id | AUTONUMBER | set in the global pool | Foreign Key referencing set_id from Question Set | |
| cpool_question | course_pool_id | AUTONUMBER | course pool holding the question | Foreign Key referecing course_pool_id from Course Pool | |
| cpool_question | qst_id | AUTONUMBER | question in the course pool | Foreign Key referencing qst_id from Question | |
| cpool_set | course_pool_id | AUTONUMBER | course pool holding the question set | Foreign Key referecing course_pool_id from Course Pool | |
| cpool_set | set_id | AUTONUMBER | question in the course pool | Foreign Key referencing set_id from Question Set | |
| bulletin | announcement_id | AUTONUMBER | announcement id number | Primary Key | |
| bulletin | announcement | TEXT | announcement text | ||
| course_bulletin | course_id | AUTONUMBER | course the bulletin belongs to | Foreign Key referencing course_id from Course | |
| course_bulletin | announcement_id | AUTONUMBER | announcement on the course bulletin | Foreign Key referencing announcement_id from Bulletin | |
| section_bulletin | section_id | AUTONUMBER | section the bulletin belongs to | Foreign Key referencing section_id from Section | |
| section_bulletin | announcement_id | AUTONUMBER | announcement on the section bulletin | Foreign Key referencing announcement_id from Bulletin | |
| gradebook | submission_id | AUTONUMBER | student grade book | Foreign Key referencing submission_id from Student_Submission | |
| gradebook | section_id | AUTONUMBER | section the student receives a grade in | Foreign Key referencing section_id from Section | |
| gradebook | assignment_name | VARCHAR(8) | name of assignment the student was graded on | ||
| gradebook | grade | PERCENT | grade received on an assignment | ||
| gradebook | points_earned | NUMBER | points earned out of the total points | ||
| gradebook | point_total | NUMBER | total points a student can receive on an assignment | ||
| gradebook | category | VARCHAR(15) | category of the assignment | ||
| gradebook | weight | PERCENT | weight of the assignment | ||
| gradebook | late | YES/NO | whether the assignment was submitted late | ||
| student_submission | submission_id | AUTONUMBER | student submission id number | Primary Key | |
| student_submission | std_id | VARCHAR(8) | student that answered the question | Foreign Key referencing std_id from Student | AlphaNumeric |
| student_submission | publish_id | AUTONUMBER | published question set the question was answered in | Foreign Key referencing publish_id from Published Set | |
| student_submission | submission | TEXT | question answer text | All solution code is appended to a text document | |